package News.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import News.entity.News;
import News.util.DBUtil;

public class Newsdao {
	public List<News> findTop(int cid) {
		String sql = "select nid,ntid,ntitle,nauthor,ncreateDate,npicPath,ncontent,nmodifyDate,nsummary from News  where ntid=? order  BY nmodifyDate desc LIMIT 6";
		List<News> dates1 = new ArrayList<News>();
		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, cid);
			rs = ps.executeQuery();
			while (rs.next()) {
				News s = new News();
				s.setNid(rs.getInt("nid"));
				s.setNtid(rs.getInt("ntid"));
				s.setNtitle(rs.getString("ntitle"));
				s.setNauthor(rs.getString("nauthor"));
				s.setNcreateDate(rs.getString("ncreateDate"));
				s.setNpicPath(rs.getString("npicPath"));
				s.setNcontent(rs.getString("ncontent"));
				s.setNmodifyDate(rs.getString("nmodifyDate"));
				s.setNsummary(rs.getString("nsummary"));
				dates1.add(s);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, conn);
		}
		return dates1;
	}

	public List<News> findAll() {
		String sql = "select *from News  order  BY ncreateDate desc";
		List<News> dates1 = new ArrayList<News>();
		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				News s = new News();
				s.setNid(rs.getInt("nid"));
				s.setNtid(rs.getInt("ntid"));
				s.setNtitle(rs.getString("ntitle"));
				s.setNauthor(rs.getString("nauthor"));
				s.setNcreateDate(rs.getString("ncreateDate"));
				s.setNpicPath(rs.getString("npicPath"));
				s.setNcontent(rs.getString("ncontent"));
				s.setNmodifyDate(rs.getString("nmodifyDate"));
				s.setNsummary(rs.getString("nsummary"));
				dates1.add(s);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, conn);
		}
		return dates1;
	}

	public List<News> search(int ntid, int pageNo) {
		String sql = "select nid,ntid,ntitle,nauthor,ncreateDate,npicPath,ncontent,nmodifyDate,nsummary from News  where ntid=? order  BY nmodifyDate desc LIMIT ?,5";
		List<News> dates1 = new ArrayList<News>();
		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, ntid);
			ps.setInt(2, (pageNo - 1) * 5);
			rs = ps.executeQuery();
			while (rs.next()) {
				News s = new News();
				s.setNid(rs.getInt("nid"));
				s.setNtid(rs.getInt("ntid"));
				s.setNtitle(rs.getString("ntitle"));
				s.setNauthor(rs.getString("nauthor"));
				s.setNcreateDate(rs.getString("ncreateDate"));
				s.setNpicPath(rs.getString("npicPath"));
				s.setNcontent(rs.getString("ncontent"));
				s.setNmodifyDate(rs.getString("nmodifyDate"));
				s.setNsummary(rs.getString("nsummary"));
				dates1.add(s);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, conn);
		}
		return dates1;
	}

	public News get(int id) {
		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		News s = null;
		String sql = "select * from News where nid=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			if (rs.next()) {
				s = new News();
				s.setNid(rs.getInt("nid"));
				s.setNtid(rs.getInt("ntid"));
				s.setNtitle(rs.getString("ntitle"));
				s.setNauthor(rs.getString("nauthor"));
				s.setNcreateDate(rs.getString("ncreateDate"));
				s.setNpicPath(rs.getString("npicPath"));
				s.setNcontent(rs.getString("ncontent"));
				s.setNmodifyDate(rs.getString("nmodifyDate"));
				s.setNsummary(rs.getString("nsummary"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, conn);
		}
		return s;
	}

	public int del(int id) {
		String sql = "DELETE FROM News WHERE nid=?";
		return DBUtil.executeUpdate(sql, id);
	}

	public int add(News n) {
		String sql = "INSERT INTO news(ntid,ntitle,nauthor,ncreateDate,npicPath,ncontent,nmodifyDate,nsummary) VALUES(?,?,?,?,?,?,?,?);";
		return DBUtil.executeUpdate(sql, n.getNtid(), n.getNtitle(), n.getNauthor(), n.getNcreateDate(),
				n.getNpicPath(), n.getNcontent(), n.getNmodifyDate(), n.getNsummary());
	}

	public int update(News s) {
		String sql = "update News set ntid=?,ntitle=?,nauthor=?,npicPath=?,ncontent=?,nmodifyDate=?,nsummary=? where nid=?";
		return DBUtil.executeUpdate(sql, s.getNtid(), s.getNtitle(), s.getNauthor(), s.getNpicPath(), s.getNcontent(),
				s.getNmodifyDate(), s.getNsummary(), s.getNid());

	}

}